package com.tea.dao;

import com.tea.util.DBUtil;
import com.tea.entity.Product;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;


public class ProductDao {
    /**
     * 根据分类ID查询商品
     */
    public List<Product> getByCategory(Integer categoryId) throws Exception {
        List<Product> products = new ArrayList<>();
        String sql = "SELECT product_id, name, description, price, main_image, category_id, stock FROM product WHERE category_id = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setLong(1, categoryId);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    products.add(mapProduct(rs));
                }
            }
        }
        return products;
    }

    /**
     * 查询所有商品
     */
    public List<Product> getAll() throws Exception {
        List<Product> products = new ArrayList<>();
        String sql = "SELECT product_id, name, description, price, main_image, category_id, stock FROM product";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) {
                products.add(mapProduct(rs));
            }
        }
        return products;
    }

    /**
     * 根据商品ID查询商品详情
     */
    public Product getProductById(Long productId) throws Exception {
        String sql = "SELECT product_id, name, description, price, main_image, category_id, stock FROM product WHERE product_id = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setLong(1, productId);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return mapProduct(rs);
                }
            }
        }
        return null;
    }

    /**
     * 将ResultSet映射为商品Map
     */
    private Product mapProduct(ResultSet rs) throws Exception {
        Product product = new Product();
        product.setProductId(rs.getLong("product_id"));
        product.setName(rs.getString("name"));
        product.setDescription(rs.getString("description"));
        product.setPrice(rs.getDouble("price"));
        product.setMainImage(rs.getString("main_image"));
        product.setStock(rs.getInt("stock"));
        product.setCategoryId(rs.getLong("category_id"));
        return product;
    }

    /**
     * 添加新产品
     */
    public boolean addProduct(Product product) throws Exception {
        String sql = "INSERT INTO product (name, description, price, main_image, category_id, stock) VALUES (?, ?, ?, ?, ?, ?)";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, product.getName());
            pstmt.setString(2, product.getDescription());
            pstmt.setDouble(3, product.getPrice());
            pstmt.setString(4, product.getMainImage());
            pstmt.setLong(5, product.getCategoryId());
            pstmt.setInt(6, product.getStock());
            return pstmt.executeUpdate() > 0;
        }
    }

    /**
     * 根据ID删除产品
     */
    public boolean deleteProduct(int productId) throws Exception {
        String sql = "DELETE FROM product WHERE product_id = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, productId);
            return pstmt.executeUpdate() > 0;
        }
    }
}